import pandas as pd
import numpy as np
import os
import datetime
import Core.Gadget as Gadget
import Core.Config as Config
from SystematicFactors.General import Save_Systematic_Factor_To_Database

def Calc_Low_Price_Stock(database, datetime1, datetime2, low_price=5, resample="weekly"):
    #
    sort = [("DateTime", 1)]
    documents = database.Find("financial_data", "stock_dailybar", filter={"symbol": "000001.SZ"}, projection=["date", "close"], sort=sort)
    df_dailybars = Gadget.DocumentsToDataFrame(documents)
    df_dailybars["date"] = pd.to_datetime(df_dailybars["date"])
    #
    df_dailybars = df_dailybars[(df_dailybars["date"] >= datetime1) & (df_dailybars["date"] <= datetime2)]
    #
    if df_dailybars.empty:
        return pd.DataFrame()
    #
    last_date = df_dailybars.iloc[-1]["date"]
    #
    df_dailybars["date_t"] = df_dailybars["date"]
    df_dailybars.set_index("date_t", inplace=True)

    if resample == "weekly":
        df_monthly = df_dailybars.resample("W").last()
    else:
        df_monthly = df_dailybars.resample("ME").last()
    #
    data = []
    for index, row in df_monthly.iterrows():
        print(row["date"])
        report_date = index
        release_date = row["date"]
        if str(release_date) == "NaT":
            continue

        documents = database.Find("financial_data", "stock_dailybar", filter={"date": release_date}, projection=["date", "symbol", "close"])
        df_total_stocks = Gadget.DocumentsToDataFrame(documents)
        # print(df_total_stocks)
        df_hige_price = df_total_stocks[df_total_stocks["close"] >= low_price]
        count_total = len(df_total_stocks)
        count_high = len(df_hige_price)
        ratio = count_high / count_total
        data.append([report_date, release_date, count_high, count_total, ratio, 1-ratio])
    #
    df = pd.DataFrame(data, columns=["report_date", "date", "above_5", "total", "above_5_ratio", "under_5_ratio"])
    df["under_5"] = df["total"] - df["above_5"]
    return df


def Calc_Low_Price_Stock_Ratio(database, datetime1, datetime2):
    #
    df_weekly = Calc_Low_Price_Stock(database, datetime1, datetime2, low_price=5, resample="weekly")
    if df_weekly.empty:
        print("No Data, Low_Price_Stock_Ratio")
    df_weekly["Release_Date"] = df_weekly["date"]
    df_weekly["dif"] = df_weekly["under_5_ratio"].diff(1)
    #
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name='Low_Price_Ratio_Weekly',
                                       field_name='under_5_ratio')
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name='Low_Price_Ratio_Weekly_Dif', field_name='dif')
    #
    df_monthly = Calc_Low_Price_Stock(database, datetime1, datetime2, low_price=5, resample="monthly")
    df_monthly["Release_Date"] = df_monthly["date"]
    df_monthly["dif"] = df_monthly["under_5_ratio"].diff(1)
    #
    Save_Systematic_Factor_To_Database(database, df_monthly, save_name='Low_Price_Ratio_Monthly', field_name='under_5_ratio')
    Save_Systematic_Factor_To_Database(database, df_monthly, save_name='Low_Price_Ratio_Monthly_Dif', field_name='dif')


def LowPriceStock(database, datetime1, datetime2):
    df = Calc_Low_Price_Stock(database, datetime1, datetime2, low_price=5, resample="ME")
    last_date = df.iloc[-1]["date"]
    s_last_date = Gadget.ToDateString(last_date)

    path = r"C:\Users\kkwoo\Documents\Pace\Low_Price_Stock\\"
    # df.to_csv(path + "No_More_Low_Price_" + s_last_date + ".csv")
    writer = pd.ExcelWriter(path + "No_More_Low_Price_" + datetime1.strftime("%Y-%m-%d") + "_" + datetime2.strftime("%Y-%m-%d") + ".xlsx")
    df.to_excel(writer, 'low_price_stock')
    writer.close()


if __name__ == '__main__':
    #
    # from Core.Config import *
    # pathfilename = os.getcwd() + "\..\Config\config2.json"
    # config = Config(pathfilename)
    # database = config.DataBase("JDMySQL")
    # realtime = config.RealTime()

    path_filename = os.getcwd() + "\..\Config\config_local.json"
    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")

    datetime1 = datetime.datetime(2005, 1, 1)
    datetime2 = datetime.datetime(2024, 9, 30)

    LowPriceStock(database, datetime1, datetime2)
    # Calc_Low_Price_Stock_Ratio(database, datetime1, datetime2)